package com.mairuide.one_click_search.service;

import com.mairuide.one_click_search.dao.DataTemplateDao;
import com.mairuide.one_click_search.dao.DataTemplateFieldDao;
import com.mairuide.one_click_search.dto.DataTemplateDto;
import com.mairuide.one_click_search.pojo.DataSource;
import com.mairuide.one_click_search.pojo.DataTemplate;
import com.mairuide.one_click_search.pojo.DataTemplateField;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @Description
 * @auther 刘中华
 * @create 2019-04-23 16:14
 */
@Service
@Transactional
public class DataTemplateService {
    @Autowired
    DataTemplateDao dataTemplateDao;

    @Autowired
    DataTemplateFieldDao dataTemplateFieldDao;

    @Autowired
    DataTemplateFieldService dataTemplateFieldService;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<DataTemplate> findAll(){
        String sql="select * from TB_DATA_TEMPLATE where is_delete=0";
        List<DataTemplate> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<DataTemplate>(DataTemplate.class));
        return list;
    }

    public DataTemplate findOneById(Integer vid){
        DataTemplate dataTemplate = dataTemplateDao.findOne(vid);
        return dataTemplate;
    }
    /**
     * @Author 刘中华
     * @Date 2019/4/26 14:58
     * @Param [vid]
     * @return com.mairuide.one_click_search.dto.DataTemplateDto
     * @Description  根据模板id查找dto
     **/
    public DataTemplateDto findDataTemplateDtoById(Integer vid){
        DataTemplate dataTemplate = dataTemplateDao.findOne(vid);
        List<DataTemplateField> list = dataTemplateFieldService.findDataTemplateFieldListByTableName(dataTemplate.getTablename());
        DataTemplateDto dataTemplateDto = new DataTemplateDto();
        BeanUtils.copyProperties(dataTemplate,dataTemplateDto);
        dataTemplateDto.setDataTemplateFieldList(list);
        return dataTemplateDto;
    }
    /**
     * @Author 刘中华
     * @Date 2019/4/26 14:58
     * @Param [vid]
     * @return com.mairuide.one_click_search.dto.DataTemplateDto
     * @Description  根据模板id查找模板
     **/
    public DataTemplate findDataTemplateById(Integer vid){
        return dataTemplateDao.findOne(vid);
    }
    /**
     * @Author 刘中华
     * @Date 2019/4/26 11:06
     * @Param [dataTemplateDto]
     * @return void
     * @Description 得到分页数据
     **/
    public Page<DataTemplate> getPageData(DataTemplateDto dataTemplateDto, int page, int size){
        Sort sort = new Sort(Sort.Direction.DESC,"vid");
        Specification<DataTemplate> specification = createSpecification(dataTemplateDto);
        PageRequest pageRequest = new PageRequest(page-1<0?0:page-1,size,sort);
        return dataTemplateDao.findAll(specification, pageRequest);
    }
    /**
     * 动态条件构建
     * @param searchMap
     * @return
     */
    private Specification<DataTemplate> createSpecification(DataTemplateDto dataTemplateDto) {
        return new Specification<DataTemplate>() {
            @Override
            public Predicate toPredicate(Root<DataTemplate> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> predicateList = new ArrayList<Predicate>();
                if (StringUtils.isNotBlank(dataTemplateDto.getMobanname())) {
                    predicateList.add(cb.like(root.get("mobanname").as(String.class), "%"+dataTemplateDto.getMobanname()+"%"));
                }
                predicateList.add(cb.equal(root.get("isDelete").as(Integer.class), 0));
                return cb.and( predicateList.toArray(new Predicate[predicateList.size()]));
            }
        };

    }
    /**
     * @Author 刘中华
     * @Date 2019/4/23 16:59
     * @Param
     * @return  保存模板  和 模板字段  表
     * @Description
     **/
    public void addDataTemplate(DataTemplateDto dataTemplateDto){
        List<DataTemplateField> dataTemplateFieldList = dataTemplateDto.getDataTemplateFieldList();
        DataTemplate dataTemplate = new DataTemplate();
        if (dataTemplateDto.getVid()!=null){ //修改
            DataTemplate one = dataTemplateDao.findOne(dataTemplateDto.getVid());
            String tablename = one.getTablename();
            List<DataTemplateField> list = dataTemplateFieldService.findDataTemplateFieldListByTableName(tablename);
            Map<Integer, DataTemplateField> fieldMap = list.stream().collect(Collectors.toMap(DataTemplateField::getVid, a -> a, (k1, k2) -> k1));
            for (int i = 0; i < dataTemplateFieldList.size(); i++) {
                DataTemplateField field = dataTemplateFieldList.get(i);
                field.setTablename(dataTemplateDto.getTablename());
                field.setIsDelete(fieldMap.get(field.getVid()).getIsDelete());
                field.setCreateTime(fieldMap.get(field.getVid()).getCreateTime());
            }
            BeanUtils.copyProperties(dataTemplateDto,dataTemplate,"isDelete","createTime");
            dataTemplate.setCreateTime(one.getCreateTime());
            dataTemplate.setIsDelete(one.getIsDelete());
        }else{ //新增
            for (int i = 0; i < dataTemplateFieldList.size(); i++) {
                DataTemplateField field = dataTemplateFieldList.get(i);
                field.setCreateTime(new Timestamp(System.currentTimeMillis()));
                field.setIsDelete(0);
                field.setTablename(dataTemplateDto.getTablename());
            }
            BeanUtils.copyProperties(dataTemplateDto,dataTemplate);
            dataTemplate.setIsDelete(0);
            dataTemplate.setCreateTime(new Timestamp(System.currentTimeMillis()));
        }
        dataTemplateDao.save(dataTemplate);
        dataTemplateFieldDao.save(dataTemplateFieldList);
    }
    /**
     * @Author 刘中华
     * @Date 2019/4/28 10:45
     * @Param [dataTemplateDto]
     * @return void
     * @Description  新增模板
     **/
    public void saveTemplate(DataTemplate dataTemplate) throws Exception{

        if (dataTemplate.getVid()==null){  //新增
            if (!checkTempleteCodeIsExists(dataTemplate.getTablename())){
                throw new Exception("模板标识字段已存在");
            }
            dataTemplate.setCreateTime(new Timestamp(System.currentTimeMillis()));
            dataTemplate.setIsDelete(0);
            DataTemplate save = dataTemplateDao.save(dataTemplate);
        }else{
            DataTemplate template = dataTemplateDao.findOne(dataTemplate.getVid());
            BeanUtils.copyProperties(dataTemplate,template,"isDelete","createTime");
            DataTemplate save = dataTemplateDao.save(template);
        }

    }
    /**
     * @Author 刘中华
     * @Date 2019/5/6 15:33
     * @Param [code]
     * @return boolean
     * @Description 检查模板标识是否唯一
     **/
    public boolean checkTempleteCodeIsExists(String code) throws Exception{
        if (StringUtils.isBlank(code)){
            throw new Exception("模板标识字段不能为null");
        }
        String sql = "select * from TB_DATA_TEMPLATE where IS_DELETE = 0 and TABLENAME = '"+code+"'";
        List<DataTemplate> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DataTemplate.class));
        return query.size()==0?true:false;
    }

    /**
     * @Author 刘中华
     * @Date 2019/4/23 16:59
     * @return  删除模板  和 模板字段  表
     * @Description
     **/
    public void delDataTemplate(Integer vid){
        DataTemplate dataTemplate = dataTemplateDao.findOne(vid);
        if (dataTemplate!=null){
            String tablename = dataTemplate.getTablename();
            String sql= "update TB_DATA_TEMPLATE_FIELD set IS_DELETE = 1 where TABLENAME= '"+tablename+"'";
            String sql2= "update TB_DATA_TEMPLATE set IS_DELETE = 1 where vid= "+vid;
            jdbcTemplate.execute(sql);
            jdbcTemplate.execute(sql2);
        }
    }
    /**
     * @Author 刘中华
     * @Date 2019/4/24 14:34
     * @Param [vid]  DataTemplate的主键   DataTemplateField::getTablename;
     * @return java.util.Map<java.lang.String,com.mairuide.one_click_search.pojo.DataTemplateField>
     * @Description
     **/
    public Map<String, DataTemplateField> getDataTemplateFieldMap(Integer vid){
        String sql="select * from TB_DATA_TEMPLATE_FIELD WHERE is_delete= 0 and TABLENAME = (select TABLENAME from TB_DATA_TEMPLATE WHERE vid = "+vid+")";
        List<DataTemplateField> fields = jdbcTemplate.query(sql, new BeanPropertyRowMapper<DataTemplateField>(DataTemplateField.class));
        Map<Integer, DataTemplateField> fieldMap = fields.stream().collect(Collectors.toMap(DataTemplateField::getCol, a -> a, (k1, k2) -> k1));
        HashMap<String, DataTemplateField> map = new HashMap<>();
        for (Map.Entry<Integer, DataTemplateField> entry : fieldMap.entrySet()) {
            map.put(getColName(String.valueOf(entry.getKey())),entry.getValue());
        }
        return map;
    }

    public void batchSave(List<DataSource> list) {
        if (list==null||list.size()==0) return;
        final List<DataSource> tempList = list;
        String sql="insert into TB_DATA_SOURCE(vid, TABLENAME, IS_DELETE, CREATE_TIME, CREATE_ID, "
                + "F01, F02, F03, F04, F05, F06, F07, F08, F09, F10,F11, F12, F13, F14,F15, F16, F17, F18,F19, F20,"
                + "F21, F22, F23, F24, F25, F26, F27, F28, F29, F30,F31, F32, F33, F34,F35, F36, F37, F38,F39, F40 ,UUID) ";
        sql+="values(SEQ_DATA_SOURCE.nextval,?,?,?,?,"
                + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
                + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.batchUpdate(sql,
                new BatchPreparedStatementSetter() {
                    public void setValues(PreparedStatement ps, int i)
                            throws SQLException {
                        ps.setString(1, tempList.get(i).getTablename()==null?"":tempList.get(i).getTablename());
                        ps.setInt(2, tempList.get(i).getIsDelete()==null?0:tempList.get(i).getIsDelete());
                        ps.setTimestamp(3, tempList.get(i).getCreateTime()==null?null:tempList.get(i).getCreateTime());
                        ps.setInt(4, tempList.get(i).getCreateId()==null?0:tempList.get(i).getCreateId());

                        ps.setString(5, tempList.get(i).getF01()==null?"":tempList.get(i).getF01());
                        ps.setString(6, tempList.get(i).getF02()==null?"":tempList.get(i).getF02());
                        ps.setString(7, tempList.get(i).getF03()==null?"":tempList.get(i).getF03());
                        ps.setString(8, tempList.get(i).getF04()==null?"":tempList.get(i).getF04());
                        ps.setString(9, tempList.get(i).getF05()==null?"":tempList.get(i).getF05());
                        ps.setString(10, tempList.get(i).getF06()==null?"":tempList.get(i).getF06());
                        ps.setString(11, tempList.get(i).getF07()==null?"":tempList.get(i).getF07());
                        ps.setString(12, tempList.get(i).getF08()==null?"":tempList.get(i).getF08());
                        ps.setString(13, tempList.get(i).getF09()==null?"":tempList.get(i).getF09());
                        ps.setString(14, tempList.get(i).getF10()==null?"":tempList.get(i).getF10());
                        ps.setString(15, tempList.get(i).getF11()==null?"":tempList.get(i).getF11());
                        ps.setString(16, tempList.get(i).getF12()==null?"":tempList.get(i).getF12());
                        ps.setString(17, tempList.get(i).getF13()==null?"":tempList.get(i).getF13());
                        ps.setString(18, tempList.get(i).getF14()==null?"":tempList.get(i).getF14());
                        ps.setString(19, tempList.get(i).getF15()==null?"":tempList.get(i).getF15());
                        ps.setString(20, tempList.get(i).getF16()==null?"":tempList.get(i).getF16());
                        ps.setString(21, tempList.get(i).getF17()==null?"":tempList.get(i).getF17());
                        ps.setString(22, tempList.get(i).getF18()==null?"":tempList.get(i).getF18());
                        ps.setString(23, tempList.get(i).getF19()==null?"":tempList.get(i).getF19());
                        ps.setString(24, tempList.get(i).getF20()==null?"":tempList.get(i).getF20());
                        ps.setString(25, tempList.get(i).getF21()==null?"":tempList.get(i).getF21());
                        ps.setString(26, tempList.get(i).getF22()==null?"":tempList.get(i).getF22());
                        ps.setString(27, tempList.get(i).getF23()==null?"":tempList.get(i).getF23());
                        ps.setString(28, tempList.get(i).getF24()==null?"":tempList.get(i).getF24());
                        ps.setString(29, tempList.get(i).getF25()==null?"":tempList.get(i).getF25());
                        ps.setString(30, tempList.get(i).getF26()==null?"":tempList.get(i).getF26());
                        ps.setString(31, tempList.get(i).getF27()==null?"":tempList.get(i).getF27());
                        ps.setString(32, tempList.get(i).getF28()==null?"":tempList.get(i).getF28());
                        ps.setString(33, tempList.get(i).getF29()==null?"":tempList.get(i).getF29());
                        ps.setString(34, tempList.get(i).getF30()==null?"":tempList.get(i).getF30());
                        ps.setString(35, tempList.get(i).getF31()==null?"":tempList.get(i).getF31());
                        ps.setString(36, tempList.get(i).getF32()==null?"":tempList.get(i).getF32());
                        ps.setString(37, tempList.get(i).getF33()==null?"":tempList.get(i).getF33());
                        ps.setString(38, tempList.get(i).getF34()==null?"":tempList.get(i).getF34());
                        ps.setString(39, tempList.get(i).getF35()==null?"":tempList.get(i).getF35());
                        ps.setString(40, tempList.get(i).getF36()==null?"":tempList.get(i).getF36());
                        ps.setString(41, tempList.get(i).getF37()==null?"":tempList.get(i).getF37());
                        ps.setString(42, tempList.get(i).getF38()==null?"":tempList.get(i).getF38());
                        ps.setString(43, tempList.get(i).getF39()==null?"":tempList.get(i).getF39());
                        ps.setString(44, tempList.get(i).getF40()==null?"":tempList.get(i).getF40());
                        ps.setString(45, tempList.get(i).getUuid()==null?"":tempList.get(i).getUuid());
                    }
                    public int getBatchSize() {
                        return tempList.size();
                    }
                });
    }


    public static String getColName(String s){
        HashMap<String, String> map = new HashMap<>();
        map.put("0","A");map.put("1","B");map.put("2","C");map.put("3","D");map.put("4","E");map.put("5","F");map.put("6","G");
        map.put("7","H");map.put("8","I");map.put("9","J");map.put("10","K");map.put("11","L");map.put("12","M");map.put("13","N");
        map.put("14","O");map.put("15","P");map.put("16","Q");map.put("17","R");map.put("18","S");map.put("19","T");map.put("20","U");
        map.put("21","V");map.put("22","W");map.put("23","X");map.put("24","Y");map.put("25","Z");map.put("26","AA");map.put("27","AB");
        map.put("28","AC");map.put("29","AD");map.put("30","AE");map.put("31","AF");map.put("32","AG");map.put("33","AH");map.put("34","AI");
        map.put("35","AK");map.put("36","AL");map.put("37","AM");map.put("38","AN");map.put("39","AO");
        return map.get(s);
    }
}
